package com.ce.common.db.handler;

import static org.junit.Assert.assertEquals;

import java.sql.ResultSet;
import java.util.List;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import com.ce.common.db.base.DBConnection;
import com.ce.common.db.base.DataObject;
import com.ce.common.db.base.Database;
import com.ce.common.db.base.JDBCConnection;
import com.ce.common.db.base.PropertiesUtil;
import com.ce.common.db.execution.SQLFileExecution;
import com.ce.common.db.execution.impl.MySQLFileExecution;
import com.ce.common.db.handler.TableHandler;
import com.ce.common.db.handler.impl.MySQLTableHandler;
import com.ce.common.db.loader.DatabaseLoader;
import com.ce.common.db.loader.impl.DatabaseLoaderImpl;

/**
 * 处理表的操作接口的测试类
 * @author yangenxiong
 *
 */
public class TableHandlerT {
	
	TableHandler handler;
	
	DatabaseLoader loader;
	
	SQLFileExecution execution;
	
	Database db;
	
	DBConnection conn;
	
	@Before
	public void setUp() throws Exception {
		
		db = new Database(PropertiesUtil.JDBC_USERNAME, PropertiesUtil.JDBC_PASSWD, 
				PropertiesUtil.JDBC_DATABASE, "localhost", "UTF8");
		conn = JDBCConnection.getJDBCConnection();
		handler = new MySQLTableHandler(conn);
		loader = new DatabaseLoaderImpl(conn);
		execution = new MySQLFileExecution(db);
		
	}
	
	@After
	public void tearDown() {
		db = null;
		handler = null;
		loader = null;
		execution = null;
	}
	
	/**
	 * 测试删除表
	 */
	@Test
	public void testDropTables() throws Exception {
		revert();
		
		
		//测试删除符合前缀的表和视图
		boolean result = handler.dropTables(loader.getTablesByPrefixs(new String[]{"TABLE", "VIEW"}, 
				new String[]{"jbpm", "org"}));
		//判断删除后整合库剩下的表和视图数
		List<DataObject> afterDrop = loader.getAllTables(new String[]{"TABLE", "VIEW"});
		assertEquals(afterDrop.size(), 201);
		
		//将数据库还原到准备数据前状态, 方便以后测试
		revert();
		
		
		//测试删除不符合前缀的表和视图
		result = handler.dropTables(loader.getTablesWithOutPrefixs(new String[]{"TABLE", "VIEW"}, 
				new String[]{"jbpm", "org"}));
		afterDrop = loader.getAllTables(new String[]{"TABLE", "VIEW"});
		//System.out.println(afterDrop.size());
		assertEquals(afterDrop.size(), 51);
	}
	
	/**
	 * 测试删除符合前缀的表的数据
	 */
	@Test
	public void testDeleteRecord() throws Exception {
		revert();
		
		
		//查询ORG_ORGANIZATION表, 默认情况下是有数据的
		ResultSet rs = conn.query("select * from ORG_ORGANIZATION");
		assertEquals(rs.next(), true);//先断言本来是有数据的
		conn.closeResultSet(rs);
		
		//查询ACCOUNT_ACCOUNT表, 默认情况下是有数据的
		rs = conn.query("select * from ACCOUNT_ACCOUNT");
		assertEquals(rs.next(), true);//先断言本来是有数据的
		conn.closeResultSet(rs);
		
		
		/** ================================================== */
		//删除以jbpm和org打头的表的数据, 且勿略关系
		boolean result = handler.deleteRecord(loader.getTablesByPrefixs(new String[]{"TABLE", "VIEW"}, 
				new String[]{"jbpm", "org"}), true);
		assertEquals(result, true);
		
		//删除数据后, 查询ORG_ORGANIZATION表, 看是否还有数据
		rs = conn.query("select * from ORG_ORGANIZATION");
		assertEquals(rs.next(), false);//删除后就没有数据了
		conn.closeResultSet(rs);
		
		revert();
		
		
		/** ================================================== */
		//删除不以jbpm和org打头的表的数据, 且忽略关系
		result = handler.deleteRecord(loader.getTablesWithOutPrefixs(new String[]{"TABLE", "VIEW"}, 
				new String[]{"jbpm", "org"}), true);
		assertEquals(result, true);
		
		//查询ACCOUNT_ACCOUNT表, 断言没有数据
		rs = conn.query("select * from ACCOUNT_ACCOUNT");
		assertEquals(rs.next(), false);
		conn.closeResultSet(rs);
		
		revert();
		
		/** ================================================== */
		//删除以jbpm和org打头的表的数据, 不忽略关系
		result = handler.deleteRecord(loader.getTablesByPrefixs(new String[]{"TABLE", "VIEW"}, 
				new String[]{"jbpm", "org"}), false);
		assertEquals(result, false);

		//因为删除数据失败, 所以ORG_ORGANIZATION里面还是有数据的
		rs = conn.query("select * from ORG_ORGANIZATION");
		assertEquals(rs.next(), true);//先断言本来是有数据的
		conn.closeResultSet(rs);
		
		revert();
		
		/** ================================================== */
		//删除不以jbpm和org打头的表的数据, 不忽略关系
		result = handler.deleteRecord(loader.getTablesWithOutPrefixs(new String[]{"TABLE", "VIEW"}, 
				new String[]{"jbpm", "org"}), false);
		assertEquals(result, false);
		
		//因为删除数据失败, 所以ACCOUNT_ACCOUNT里面是有数据的
		rs = conn.query("select * from ACCOUNT_ACCOUNT");
		assertEquals(rs.next(), true);
		conn.closeResultSet(rs);
	}
	
	/**
	 * 每次删除完表后, 复原test_init.sql数据, 也就是测试的准备数据
	 */
	private void revert()throws Exception {
		//执行初始化文件前, 先将整个库的表删除
		handler.dropTables(loader.getAllTables(new String[]{"TABLE", "VIEW"}));
		execution.executeFile("src/test/resources/test_init.sql");
		Thread.sleep(15000);//等待让sql文件执行成功
	}
}
